package com.skycode7.dao;

import com.skycode7.bean.NoticeBean;
import com.skycode7.util.Dbconnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NoticeDao {
    Dbconnection db = new Dbconnection();
    public int add(NoticeBean news) {
        Connection conn = db.getConn();
        String sql = "insert into notice values (null,?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, news.getNoticeTitle());
            ps.setString(2, news.getNoticeContent());
            ps.setString(3, news.getNoticeTime());
            ps.setString(4, news.getAdminName());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("插入news表异常");
        }
        return 0;
    }
    public int delete(int noticeId) {
        Connection conn = db.getConn();
        String sql = "delete from notice where noticeId = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, noticeId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除news表异常");
        }
        return 0;//如果添加异常 返回0
    }
    public int update(NoticeBean news) {
        Connection conn = db.getConn();//conn代表mysql连接
        String sql = "update notice set NoticeTitle=?,NotitleContent=?,NoticeTime=?,AdminName=? where NoticeID=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //将新闻数据对象中的数据依次绑定到sql命令中
            ps.setString(1, news.getNoticeTime());
            ps.setString(2, news.getNoticeContent());
            ps.setString(3, news.getNoticeTime());
            ps.setString(4, news.getAdminName());
            ps.setInt(5, news.getNoticeId());
            return ps.executeUpdate();//执行sql命令 完成数据插入
        } catch (SQLException e) {
            System.out.println("更新news表异常");
        }
        return 0;//如果添加异常 返回0
    }
    public List<NoticeBean> getList(int page){
        Connection conn = db.getConn();
        String sql ="select * from notice limit ?,?";
        List<NoticeBean> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*10);
            ps.setInt(2,10);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                NoticeBean news = new NoticeBean();
                news.setNoticeId(rs.getInt("NoticeId"));
                news.setNoticeTitle(rs.getString("NoticeTitle"));
                news.setNoticeContent(rs.getString("NoticeContent"));
                news.setNoticeTime(rs.getString("NoticeTime"));
                news.setAdminName(rs.getString("AdminName"));
                list.add(news);
            }
        } catch (SQLException e){
            System.out.println("查询news表异常");
        }
        return list;
    }

    public int count() {
        Connection conn = db.getConn();
        String sql = "select count(*) from notice";
        Statement statement = null;
        try {
            statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }
}
